package devbureau.fstore.common.db;

import devbureau.fstore.common.Role;
import devbureau.fstore.common.User;
import devbureau.fstore.common.UserImpl;
import devbureau.fstore.common.UserRegistry;
import devbureau.fstore.util.DBHelper;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;


// TODO : Сделать UserRegistryException

public class UserRegistryDBImpl implements UserRegistry {

   	private static Logger logger = Logger.getLogger(UserRegistryDBImpl.class);
    private static final String SQL_SELECT_USER = "select U.FIRSTNAME,U.LASTNAME,U.LOGIN,U.PASSWORD,U.IS_ACTIVE,U.ROLE_ID,R.LABEL ROLE_LABEL from STORE.USER U join STORE.ROLE R on U.ROLE_ID = R.ID where U.LOGIN = ?";
    private static final String SQL_SELECT_ALL_USERS = "select U.FIRSTNAME,U.LASTNAME,U.LOGIN,U.IS_ACTIVE,U.ROLE_ID,R.LABEL ROLE_LABEL from STORE.USER U join STORE.ROLE R on U.ROLE_ID = R.ID order by LASTNAME, FIRSTNAME, LOGIN";
    private static final String SQL_SELECT_PASSWORD = "select PASSWORD from STORE.USER where LOGIN = ? and IS_ACTIVE = 1";
    private static final String SQL_INSERT_USER = "insert into STORE.USER(LOGIN,FIRSTNAME,LASTNAME,PASSWORD,ROLE_ID) values (?,?,?,?,?)";
    private static final String SQL_UPDATE_USER = "update STORE.USER set FIRSTNAME = ? , LASTNAME = ?, PASSWORD = ? where LOGIN = ?";
    private static final String SQL_ACTIVATE_USER = "update STORE.USER set IS_ACTIVE = ? where LOGIN = ?";
    private static final String SQL_SELECT_ALL_ROLES = "select R.ID,R.LABEL from STORE.ROLE R order by LABEL";
    private static final String SQL_SELECT_ROLE = "select R.ID,R.LABEL from STORE.ROLE R where ID = ?";


    public UserRegistryDBImpl() {
    }

    public Boolean checkUserPassword(String login, String arg_password) throws Exception {
        logger.trace("#checkUserPassword enter");
        Boolean check = false;
        Connection connection = null;
        
        try{
            connection = DBHelper.getConnection();
            PreparedStatement ps = connection.prepareStatement(SQL_SELECT_PASSWORD);
            ps.setString(1, login);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                String password = rs.getString("PASSWORD");
                check = false;
                if(password.equals(arg_password)) {
                    check = true;
                }
            }
            rs.close();
            ps.close();
        }catch(Exception ex){
            logger.fatal(ex, ex);
            throw ex;
        }finally{
            try{
                connection.close();
            }catch(Exception ex){
                logger.fatal(ex);
            }
        }

        if(check == null) {
            throw new Exception("Ошибка авторизации, пользователя с логином '" +login+ "' нет в реестре.");
        }

        logger.debug("#checkUserPassword check = " + check);
        logger.trace("#checkUserPassword exit");
        return check;
    }

    public User getUser(String login) throws Exception {
        logger.trace("#getUser enter");
        logger.trace("#getUser login = " + login);

        Connection connection = null;
        UserImpl user = null;
        
        try{
            connection = DBHelper.getConnection();
            PreparedStatement ps = connection.prepareStatement(SQL_SELECT_USER);
            ps.setString(1, login);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                String firstName = rs.getString("FIRSTNAME");
                String lastName = rs.getString("LASTNAME");
                String password = rs.getString("PASSWORD");
                Integer roleId = rs.getInt("ROLE_ID");
                String roleLabel = rs.getString("ROLE_LABEL");
                Boolean active = new Boolean(rs.getInt("IS_ACTIVE")==1);

                user = new UserImpl(login, firstName, lastName, roleId,active,roleLabel);
                user.setPassword(password);
            }
            rs.close();
            ps.close();
        }catch(Exception ex){
            logger.fatal(ex, ex);
            throw ex;
        }finally{
            try{
                connection.close();
            }catch(Exception ex){
                logger.fatal(ex);
            }
        }
        logger.debug("#getUser user = " + user);
        logger.debug("#getUser exit");

        return user;
    }

    public List<User> getAllUsers(){
        logger.debug("#getAllUsers enter");
        List<User> users = new ArrayList<User>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        try{
            connection = DBHelper.getConnection();
            preparedStatement= connection.prepareStatement(SQL_SELECT_ALL_USERS);
            rs = preparedStatement.executeQuery();
            while(rs.next()){
                String fName = rs.getString("FIRSTNAME");
                String lName = rs.getString("LASTNAME");
                String login = rs.getString("LOGIN");
                Integer roleId = rs.getInt("ROLE_ID");
                Boolean active = new Boolean(rs.getInt("IS_ACTIVE")==1);
                String roleLabel = rs.getString("ROLE_LABEL");
                User user = new UserImpl(login, fName, lName, roleId,active,roleLabel);
                users.add(user);
            }
            
        }catch(Exception e){
            logger.error("Error while getting all users from DB", e);
        }finally{
            try{
                if(preparedStatement != null) preparedStatement.close();
                if(rs != null) rs.close();
                if(connection != null) connection.close();
            }catch(Exception e){
                logger.error("Error while closing connection or ps", e);
            }
        }
        return users;
    }
    

    public void registerUser(User user, String password) throws Exception {
        logger.debug("#registerUser enter");
        logger.debug("#registerUser user = " + user);

        Connection connection = null;

        try{
            connection = DBHelper.getConnection();
            PreparedStatement ps = connection.prepareStatement(SQL_INSERT_USER);
            ps.setString(1, user.getLogin());
            ps.setString(2, user.getFirstName());
            ps.setString(3, user.getLastName());
            ps.setString(4, password);
            ps.setInt(5, user.getRoleId());
            ps.executeUpdate();
            ps.close();
        }catch(Exception ex){
            logger.fatal(ex, ex);
            throw ex;
        }finally{
            try{
                connection.close();
            }catch(Exception ex){
                logger.fatal(ex);
            }
        }
        logger.debug("#registerUser exit");
    }

    public void updateUser(User user) throws Exception{
        logger.debug("updateUser["+user+"]");
        String login = user.getLogin();
        Connection connection = null;
        PreparedStatement ps  = null;
        try {
            connection = DBHelper.getConnection();
            if(user.getPassword()!=null && !user.getPassword().isEmpty()){
                ps = connection.prepareStatement(SQL_UPDATE_USER);
                ps.setString(1, user.getFirstName());
                ps.setString(2, user.getLastName());
                ps.setString(3, user.getPassword());
                ps.setString(4, user.getLogin());
            }
            int rows = ps.executeUpdate();
            ps.close();
            if(rows < 1){
                throw new Exception("Пользователь с login = " + login + " не найден");
            }
        }finally{
            try{
                if(connection!=null && !connection.isClosed()){
                    connection.close();
                }
            }catch(Exception ex){
                logger.fatal("Error while closing connection", ex);
            }
        }
    }


    public void activateUser(String login, boolean state) throws Exception{
        Connection connection = null;
        PreparedStatement ps  = null;
        try {
            connection = DBHelper.getConnection();
            ps = connection.prepareStatement(SQL_ACTIVATE_USER);
            int active = 0;
            if(state) {
                active = 1;
            }
            ps.setInt(1, active);
            ps.setString(2, login);
            int rows = ps.executeUpdate();
            ps.close();
            if(rows < 1){
                throw new Exception();
            }
        }catch (Exception e) {
            logger.error("#activate", e);
            throw new Exception("Пользователь с логином "+login+" не восстановлен.");
        }finally{
            try{
                if(connection!=null && !connection.isClosed()){
                    connection.close();
                }
            }catch(Exception ex){
                logger.fatal("Error while closing connection", ex);
            }
        }
    }

    public List<Role> getAllRoles() throws Exception {
        logger.debug("#getAllRoles enter");
        List<Role> roles = new ArrayList<Role>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        try{
            connection = DBHelper.getConnection();
            preparedStatement= connection.prepareStatement(SQL_SELECT_ALL_ROLES);
            rs = preparedStatement.executeQuery();
            while(rs.next()){
                Integer id = rs.getInt("ID");
                String label = rs.getString("LABEL");
                Role role = new Role(id,label);
                roles.add(role);
            }

        }catch(Exception e){
            logger.error("Error while getting all roles from DB", e);
            throw e;
        }finally{
            try{
                if(preparedStatement != null) preparedStatement.close();
                if(rs != null) rs.close();
                if(connection != null) connection.close();
            }catch(Exception e){
                logger.error("Error while closing connection or ps", e);
            }
        }
        return roles;
    }
    
    public Role getRole(Integer id) throws Exception{
        logger.trace("#getRole enter");
        logger.trace("#getRole id = " + id);

        Connection connection = null;
        Role user = null;

        try{
            connection = DBHelper.getConnection();
            PreparedStatement ps = connection.prepareStatement(SQL_SELECT_ROLE);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                Integer rid = rs.getInt("ID");
                String label = rs.getString("LABEL");

                user = new Role(rid, label);
            }
            rs.close();
            ps.close();
        }catch(Exception ex){
            logger.fatal(ex, ex);
            throw ex;
        }finally{
            try{
                connection.close();
            }catch(Exception ex){
                logger.fatal(ex);
            }
        }
        logger.debug("#getUser user = " + user);
        logger.debug("#getUser exit");

        return user;
        
    }

}
